import monetdb.sql
from subprocess import call
import sys
import os
sys.path.append("../") 
import GUIconfig
import logging
import monetdb.mapi as mapi
from monetdb.monetdb_exceptions import InterfaceError 

from errors import ProjectDBError

logger = logging.getLogger("MONETBACKEND")

MEMORY_LIMIT = GUIconfig.db_memory_limit # KB
 

def create_db(db_path):
    msg = ""
    try:
        #retcode = call("ulimit -m 1000000" % db_path, shell=True)
        retcode = call("monetdbd create %s" % db_path, shell=True)
        retcode = call("ulimit -m %i; monetdbd start %s" % (MEMORY_LIMIT, db_path), shell=True)
        #retcode = call("monetdbd start %s" % db_path, shell=True)
        retcode = call("monetdb create %s" % GUIconfig.db_name, shell=True)
        retcode = call("monetdb release %s" % GUIconfig.db_name, shell=True)
    
    except OSError, e:
        print >>sys.stderr, "Execution failed:", e
        msg = str(e)
        
    if retcode != 0:
        print >>sys.stderr, "Child was terminated by signal", -retcode
        if not msg :
            msg = "Could not create DB."
        return False, msg        
    else:
        msg = "DB started"
        print >>sys.stderr, "Child returned", retcode
        return True, msg
    

def start_db(db_path):
    """
    start the database. If it does not exist yet then create it.
    """
    msg = ""
    close_db(db_path, kill=True)
    if not os.path.exists(db_path):
        return create_db(db_path)  
    else:
        
        if db_connect(db_path, auto_startup_db=False) is not None:
            msg = "DB is already started"
            print __name__, "DB is already started"
            return False, msg 
            
        try:
            print __name__, "starting db"  
            #retcode = call("ulimit -m 1000000" % db_path, shell=True)
            retcode = call("ulimit -m %i; monetdbd start %s" % (MEMORY_LIMIT, db_path), shell=True)
            #retcode = call("monetdbd start %s" % db_path, shell=True)
        except OSError, e:
            print >>sys.stderr, "Execution failed:", e
            msg = str(e)
            
        if retcode != 0:
            print >>sys.stderr, "Child was terminated by signal", -retcode
            if msg:
                msg = "could not start DB" 
            
            return False, msg
            
        else:
            msg = "DB started"
            print >>sys.stderr, "Child returned", retcode
            return True, msg
    
def close_db(db_path, kill=False):
    try:
        
        if kill:
            print "killing db"
            retcode = call("monetdb kill -a", shell=True) # time out after a second
        
        retcode = call("monetdbd stop %s" % db_path, shell=True)
            
    except OSError, e:
        print >>sys.stderr, "Execution failed:", e
    
    if retcode < 0:
        print >>sys.stderr, "Child was terminated by signal", -retcode
    else:
        print >>sys.stderr, "Child returned", retcode
    

def db_connect(db_path, auto_startup_db=True):
    #conn = monetdb.sql.connect(username="monetdb", password="monetdb", hostname="localhost", database="voc")
    
    conn = None
    failed = False
    try :
        
        conn = monetdb.sql.connect(username=GUIconfig.db_username, password=GUIconfig.db_password, hostname=GUIconfig.db_hostname, database=GUIconfig.db_name)
    
    except monetdb.sql.OperationalError, e:
        print e
        failed = True
 
    if failed and auto_startup_db:
        start_db(db_path)
        try :
            conn = monetdb.sql.connect(username=GUIconfig.db_username, password=GUIconfig.db_password, hostname=GUIconfig.db_hostname, database=GUIconfig.db_name)
        except monetdb.sql.OperationalError, e2:
            print e2
            raise 
        
    return conn

 
def execute_query(query, db_path, values=None):
    conn = db_connect(db_path)
        
    output_list = ""
    try :
        #print "query", query
        conn = db_connect(db_path)
        cursor = conn.cursor()
        
        #cursor.arraysize = 1
        if values:
            cursor.execute(query, values)
        else:
            cursor.execute(query)
        #print "Executing query ", query
        #conn.execute(query)
        conn.commit()
        
        output_list = cursor.fetchall()
        
        #print "cursor msg:", cursor.messages
        
        #output
    
    except monetdb.monetdb_exceptions.InterfaceError, e:
        print e
        print "retrying with alternate parser."
        conn.rollback()
        out = conn.execute(query)
        parser = MyMapiParser(conn)
        parser.parse_block(out)
        
        output_list = parser.get_parsed_rows()
        
    except monetdb.sql.Error, e:
      
        print str(e)
        
    finally:
        cursor.close()
       
    return output_list

def execute_multiple(queries, db_path):
    #output_list = ""
    conn = db_connect(db_path)
    #cursor = conn.cursor()
    #conn.set_autocommit(False)
    #sql_script = """
    #START TRANSACTION;
    #%s;
    #COMMIT;
    #""" % ";".join(queries) 
        #exec_queries()
    #finished = True
    #print queries
    
    while 1 :
        finished = True 
        for query in queries:
    
            try:
                conn.execute(query)
                
            except monetdb.sql.Error, e:
                print str(e)
                print "removing query %s and restarting" % query
                conn.rollback()
                queries.remove(query)
                finished = False
                break
            
        if finished:
            break
        #finished = True
        #try:
    conn.commit()
        #except monetdb.sql.Error, e: 
          
    #conn.commit()
    #cursor.close()
    #conn.set_autocommit(True)
    conn.close()
        
    return #output_list

def extract_column_names(db_path, table_name):
    """
    return the columns names
    """
    
    column_names = ""
    conn = db_connect(db_path)
    
    output = conn.execute("select * from %s limit 1" % table_name)
    parser = MyMapiParser(conn)
    parser.parse_block(output)
    
    #cursor = conn.cursor()
    #cursor.execute("select id from %s limit 1 offset 0" % table_name)
    #print conn.execute("select id from %s limit 1" % table_name)
    #conn.commit()
    table_info = parser.description    
    #cursor.close()
    conn.close()
    
    if table_info:    
        column_names = [x[0] for x in table_info[1:]]
    
    return column_names




def table_size(db_path, table_name):
    """
    Return the dimensions of the table
    """
    conn = db_connect(db_path)
    cursor = conn.cursor()
    
    #query = """SELECT count(*)
    #FROM information_schema.columns
    #WHERE table_name = '%s'""" % table_name
    
    #cursor.execute("select * from %s limit 1" % table_name)
    #cursor.execute("select * from %s limit 1" % table_name)
    out = conn.execute("select * from %s limit 1" % table_name)
    parser = MyMapiParser(conn)
    parser.parse_block(out)
    #print execute_query(query, db_path)
    
    table_info = parser.description    
    
    num_cols = len(table_info)-1 # minus the id column
    
    query = "select id from %s" % table_name
    cursor.execute(query)
    cursor.rowcount
    
    num_rows = cursor.rowcount
    
    return num_rows, num_cols 
    

class MyMapiParser(monetdb.sql.cursors.Cursor):
    
    def __init__(self, conn):
        monetdb.sql.cursors.Cursor.__init__(self,conn)
        
         # the type converters
        self.__pythonizer = monetdb.sql.converters.Pythonizer(use_unicode=self.connection.use_unicode)
        self.__monetizer = monetdb.sql.converters.Monetizer()
    #    self.messages = []
    
    def parse_block(self, block):
        
        lines = block.split("\n")
        firstline = lines[0]
    
        while firstline.startswith(mapi.MSG_INFO):
            logger.info(firstline[1:])
            self.messages.append((Warning, firstline[1:]))
            lines = lines[1:]
            firstline = lines[0]
    
        if firstline.startswith(mapi.MSG_QTABLE):
            (id, rowcount, columns, tuples) = firstline[2:].split()
            columns = int(columns)   # number of columns in result
            rowcount = int(rowcount) # total number of rows
            tuples = int(tuples)     # number of rows in this set
            rows = []
    
            # set up fields for description
            empty = [None]*columns
            table_name = empty
            column_name = empty
            type = empty
            display_size = empty
            internal_size = empty
            precision = empty
            scale = empty
            null_ok = empty
    
            for line in lines[1:]:
                if line.startswith(mapi.MSG_HEADER):
                    (data, identity) = line[1:].split("#")
                    values = [x.strip() for x in data.split(",")]
                    identity = identity.strip()
    
                    if identity == "table_name":
                        table_name = values   # not used
                    elif identity == "name":
                        column_name = values
                    elif identity == "type":
                        type = values
                    elif identity == "length":
                        internal_size = [int(x) for x in values]
                        display_size = internal_size
                    else:
                        self.messages.append((InterfaceError,
                            "unknown header field"))
                        self.__exception_handler(InterfaceError,
                                "unknown header field")
    
                    self.description = list(zip(column_name, type,
                        display_size, internal_size, precision, scale, null_ok))
    
                if line.startswith(mapi.MSG_TUPLE):
                    values = self.__parse_tuple(line)
                    rows.append(values)
    
                elif line == mapi.MSG_PROMPT:
                    self.__query_id = id
                    self.__rows = rows
                    self.__offset = 0
    
                    self.rowcount = rowcount
                    self.lastrowid = None
                    logger.debug("II store result finished")
                    return
    
        elif firstline.startswith(mapi.MSG_QBLOCK):
            rows = []
            for line in lines[1:]:
                if line.startswith(mapi.MSG_TUPLE):
                    values = self.__parse_tuple(line)
                    rows.append(values)
                elif line == mapi.MSG_PROMPT:
                    logger.debug("II store result finished")
                    self.__rows = rows
                    return
    
        elif firstline.startswith(mapi.MSG_QSCHEMA):
           if lines[1] == mapi.MSG_PROMPT:
                self.__rows = []
                self.__offset = 0
                self.description = None
                self.rowcount = -1
                self.lastrowid = None
                logger.debug("II schema finished")
                return
    
        elif firstline.startswith(mapi.MSG_QUPDATE):
           if lines[1] == mapi.MSG_PROMPT:
                (affected, identity) = firstline[2:].split()
                self.__rows = []
                self.__offset = 0
                self.description = None
                self.rowcount = int(affected)
                self.lastrowid = int(identity)
                self.__query_id = -1
                logger.debug("II update finished")
                return
    
        elif firstline.startswith(mapi.MSG_ERROR):
            self.__exception_handler(ProgrammingError, firstline[1:])
    
        elif firstline.startswith(mapi.MSG_QTRANS):
           if lines[1] == mapi.MSG_PROMPT:
                self.__rows = []
                self.__offset = 0
                self.description = None
                self.rowcount = -1
                self.lastrowid = None
                logger.debug("II transaction finished")
                return
    
        elif firstline.startswith(mapi.MSG_PROMPT):
            self.__query_id = -1
            self.__rows = []
            self.__offset = 0
    
            self.rowcount = 0
            self.lastrowid = None
            logger.debug("II empty response, assuming everything is ok")
            return
    
        # you are not supposed to be here
        self.__exception_handler(InterfaceError, "Unknown state, %s" % block)


    def get_parsed_rows(self):
        return self.__rows
        

    def __parse_tuple(self, line):
        """ parses a mapi data tuple, and returns a list of python types"""

        # values in a row are seperated by \t
        #print "desc", self.description
        
        elements = line[1:-1].split(',')
        #print "elements", elements
        if len(elements) == len(self.description):
            return tuple([self.__pythonizer.convert(element.strip("\x00 \t"),
                description[1]) for (element, description) in
                zip(elements, self.description)])
        else:
            self.__exception_handler(InterfaceError,
                    "length of row doesn't match header")


    def scroll(self, value, mode='relative'):
        """Scroll the cursor in the result set to a new position according
        to mode.

        If mode is 'relative' (default), value is taken as offset to
        the current position in the result set, if set to 'absolute',
        value states an absolute target position.

        An IndexError is raised in case a scroll operation would
        leave the result set.
        """
        self.__check_executed()

        if mode not in ['relative', 'absolute']:
            self.__exception_handler(ProgrammingError,
                    "unknown mode '%s'" % mode)

        if mode == 'relative':
            value = self.rownumber + value

        if value > self.rowcount:
             self.__exception_handler(IndexError,
                     "value beyond length of resultset")

        self.__offset = value
        end = min(self.rowcount, self.rownumber + self.arraysize)
        amount = end - self.__offset
        command = 'Xexport %s %s %s' % (self.__query_id,
                self.__offset, amount)
        block = self.connection.command(command)
        self.__store_result(block)


    def __exception_handler(self, exception_class, message):
        """ raises the exception specified by exception, and add the error
        to the message list """
        self.messages.append((exception_class, message))
        raise exception_class(message)



